#!python3
#！coding = utf-8
# AES-demo
import pymysql
import base64
from Crypto.Cipher import AES

'''
采用AES对称加密算法
'''
# str不是16的倍数那就补足为16的倍数


def connect_db(db_name):
    return pymysql.connect(host='127.0.0.1',
                           port=3306,
                           user='root',
                           password=base64.b64decode('end6eEBYR1lE'),
                           database=db_name)

#end6eEBYR1lE
def disconnect_db(conn):
    conn.close()


def read_record(conn, table_name, cols_name):
    cur = conn.cursor()
    sql_str = {"SELECT {0} from {1};".format(','.join(cols_name),table_name)}
    for s in sql_str:
        cur.execute(s)
    # cur.scroll(0,mode='absolute')
    rows = cur.fetchall()
    cols = cur.description
    cols = [i[0] for i in cols]
    # print(cols)
    res = [tuple(cols)] + list(rows)
    res = [list(r) for r in res]
    #print(res)
    conn.commit()
    return res


def load_record_from_txt(csv_file):
    print('正在装载txt数据...')
    with open(csv_file, "rb") as f:
        res = f.readlines()
		#linux_head='\ufeff'
		#windows_bom='\xef\xbb\xbf'
        res = [list(r.decode().replace('\ufeff', '').strip().split('\t')) for r in res]
    #print(res[0])
    return res


def encrpt_record(key, res):
    print('正在e数据...')
    for i, r in enumerate(res[1:]):
        for j, s in enumerate(res[i]):
            res[i + 1][j] = encrypt_mysql(key, res[i + 1][j])
    for r in res:
        pass
		#print(r)
    return res


def write_record(conn, table_name, res):
    print('正在写数据...')
    cols_name = res[0]
    #print(cols_name)
    cols_len = len(cols_name)
    rows = [tuple(r) for r in res[1:]]
    cur = conn.cursor()
    sql_str = "delete from {0}".format(table_name)
    cur.execute(sql_str)
    conn.commit()
    sql_str = "insert into {0} ({1}) VALUES({2})".format(table_name, ','.join(cols_name), ','.join(['%s' for i in range(cols_len)]))
    cur.executemany(sql_str, rows)
    conn.commit()
    # 可以把指针复位
    # cur.scroll(0,mode='absolute')
    return cols_name


def decrpt_record(key, res):
    print('正在d数据...')
    for i, r in enumerate(res[1:]):
        for j, s in enumerate(res[i]):
            res[i + 1][j] = decrypt_mysql(key, res[i + 1][j])
    return res


def add_record(db_name, table_name):
    pass


def add_to_16(value):
    # 支持中文
    while len(value.encode('utf-8')) % 16 != 0:
        value += '\0'
    return str.encode(value, 'utf-8')  # 返回bytes


def encrypt_mysql(key, text):
    # 加密方法
    # 初始化加密器
    aes = AES.new(add_to_16(key), AES.MODE_ECB)
    # 先进行aes加密
    encrypt_aes = aes.encrypt(add_to_16(text))
    # 用base64转成字符串形式
    encrypted_text = str(base64.encodebytes(encrypt_aes), encoding='utf-8').strip('\n')  # 执行加密并转码返回bytes
    # print(encrypted_text)
    return encrypted_text


def decrypt_mysql(key, text):
    # 解密方法
    # 初始化解密器
    aes = AES.new(add_to_16(key), AES.MODE_ECB)
    # 优先逆向解密base64成bytes
    base64_decrypted = base64.decodebytes(text.encode(encoding='utf-8'))
    # 执行解密并转码返回str
    decrypted_text = str(aes.decrypt(base64_decrypted), encoding='utf-8').replace('\0', '')
    return decrypted_text

if __name__ == '__main__':
    #end6eEBYR1lE
    key = str(base64.b64decode('end6eEBYR1lE'), 'utf-8')
    text = '我是谁'
    decrypt_mysql(key, encrypt_mysql(key, text))
    db_name = "h5yxzs"
    table_name = "test"
    csv_file = 'yhxx.txt'
    res = encrpt_record(key, load_record_from_txt(csv_file))
    conn = connect_db(db_name)
    cols_name = write_record(conn, table_name, res)
    #print(cols_name)
    res = read_record(conn, table_name, cols_name)
    res = decrpt_record(key, res)
    #print(res)
    disconnect_db(conn)
